<?php 
	class Questions extends CI_Model{

	 	function __construct(){
        	parent::__construct();
    	}

    	function get_question_list($limit = 0, $offset = 10, $conditions = array()){
    		$where = "q.status = 1";
            $order = "ORDER BY q.id DESC";
            $group = '';
    		if(count($conditions) > 0){
    			if(!empty($conditions['category_id'])){
    				$where .= " AND q.category_id = '{$conditions['category_id']}'";
    			}
    			if(!empty($conditions['school_id'])){
                    $where .= " AND q.school_id = '{$conditions['school_id']}'";
                }
                if(!empty($conditions['uid'])){
                    $where .= " AND q.uid = '{$conditions['uid']}'";
                }
                if(!empty($conditions['type'])){
                    switch ($conditions['type']) {
                        case 1:
                            $order = "ORDER BY q.id DESC";  //最新
                        break;
                        case 2:
                            $order = "ORDER BY hot_index DESC";  //热门
                        break;
                        case 3:
                            $order = "ORDER BY q.click_num DESC, q.answer_num DESC"; //推荐
                        break;
                        case 4:
                            $order = "ORDER BY q.id DESC"; //等待回复
                            $where .= " AND q.answer_num = 0";
                        break;
                    }
                }
                if(!empty($conditions['answer_uid'])){
                    $where .= " AND qa.uid='{$conditions['answer_uid']}'";
                }
    		}
    		$sql = "SELECT SQL_CALC_FOUND_ROWS q.*, u.nick, u.head_image_url, (q.click_num + q.answer_num * 2) AS hot_index FROM {$this->db->dbprefix}questions AS q ";
    		$sql .= "LEFT JOIN {$this->db->dbprefix}user AS u ON(u.id=q.uid) ";
            if(!empty($conditions['answer_uid'])){
                $sql .= "LEFT JOIN {$this->db->dbprefix}question_answers AS qa ON(qa.qid=q.id) ";
                $group = "GROUP BY q.id";
            }
    		$sql .= "WHERE $where $group $order LIMIT $limit, $offset ";
    		$result = $this->db->query($sql)->result_array();
			$row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
			return array("count" => $row_num['row_num'], "result" => $result);
    	}

        function increase_answer_num($id){
            $sql = "UPDATE {$this->db->dbprefix}questions SET answer_num = answer_num + 1 WHERE id='{$id}'";
            $this->db->query($sql);
        }

        function increase_click_num($id){
            $sql = "UPDATE {$this->db->dbprefix}questions SET click_num = click_num + 1 WHERE id='{$id}'";
            $this->db->query($sql);
        }

        function get_question_answer($limit = 0, $offset = 10, $conditions = array()){
            $where = 'qa.status=1';
            if(count($conditions) > 0){
                if(!empty($conditions['qid'])){
                    $where .= " AND qa.qid = '{$conditions['qid']}'";
                }
            }
            $sql = "SELECT SQL_CALC_FOUND_ROWS qa.*, u.nick, u.head_image_url FROM {$this->db->dbprefix}question_answers AS qa ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}user AS u ON(u.id=qa.uid) ";
            $sql .= "WHERE $where ORDER BY qa.add_time ASC LIMIT $limit, $offset ";
            $result = $this->db->query($sql)->result_array();
            $row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
            return array("count" => $row_num['row_num'], "result" => $result);
        }

        function get_index_question($school_id){
            $sql = "SELECT q.*, u.nick, u.head_image_url, qa.content AS a_content, (q.click_num + q.answer_num * 2) AS hot_index FROM {$this->db->dbprefix}questions AS q ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}user AS u on(u.id=q.uid) ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}question_answers AS qa ON(qa.qid=q.id) ";
            $sql .= "WHERE q.school_id = '{$school_id}' ";
            $sql .= "GROUP BY q.id ORDER BY hot_index DESC LIMIT 4 ";
            $result = $this->db->query($sql)->result_array();
            return $result;
        }
	}